package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.X_cms_GWStype;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class CMS_KazContentReport extends SvrProcess {

	// Current context
	private Properties m_ctx;
		
	private ProcessInfo pi;
	
	//Client
	private int			AD_Client_ID = 0;
	//Organization
	private int			AD_Org_ID = 0;
	//User
	private int			AD_User_ID = 0;
	//Role
	private int			AD_Role_ID = 0;
	
	private String Number = "";
	private String Series = "";
	private String IssuingCode = "";
	//private Timestamp  YearIssue = null;
	private Timestamp DateIssue = null;
	//FilePath
	private String path = "";
	
	private static CLogger	s_log	= CLogger.getCLogger (CMS_KazContentReport.class);
		
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();

		for(int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();			
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Client_ID"))
				AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("CMS_Contract_ContractNum"))
				Number = para[i].getParameter().toString();
			else if (name.equals("Series"))
				Series = para[i].getParameter().toString();
			else if (name.equals("IssuingCode"))
				IssuingCode = para[i].getParameter().toString();
			//else if (name.equals("YearIssue"))
			//	YearIssue = (Timestamp)para[i].getParameter();
			else if (name.equals("DateIssue"))
				DateIssue = (Timestamp)para[i].getParameter();		
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}				
		}
		if(path.isEmpty()){
			path = Util.localFilePath;
		}else
			path = path + System.getProperty("file.separator");
		
		AD_User_ID = Env.getAD_User_ID(getCtx());
		AD_Role_ID = Env.getAD_Role_ID(getCtx());

	}

	@Override
	protected String doIt() throws Exception {
		if(getCMS_Contract_ID() == 0)
			return "Not available for contract";
		
		String patternName = "pattern_KAZContent";
		
		//get attachment from the process
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, patternName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+patternName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path);		
		fullPath.append("Temporary CMS Register Sheets");		
		fullPath.append(fileExtension);
		
		File templateCopy = new File(fullPath.toString());
        File temporaryFile = entry.getFile(Util.localFilePath+patternName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
        } 
		catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }
		
		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Style for value column
		WritableCellFormat nn = new WritableCellFormat();
		nn.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		nn.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		nn.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		nn.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		nn.setAlignment(Alignment.CENTRE);
		nn.setVerticalAlignment(VerticalAlignment.CENTRE);
		nn.setWrap(true);
	    
		
	    WritableCellFormat valuestyle = new WritableCellFormat();
	    valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	    valuestyle.setAlignment(Alignment.CENTRE);
	    valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    valuestyle.setWrap(true);
	    // Style for name column
	    WritableCellFormat namestyle = new WritableCellFormat();
	    namestyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    namestyle.setAlignment(Alignment.LEFT);
	    namestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    namestyle.setWrap(true);
	    //Style for number column
	    WritableCellFormat numcolstyle = new WritableCellFormat();
	    numcolstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    numcolstyle.setAlignment(Alignment.RIGHT);
	    numcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    numcolstyle.setWrap(true);
	    //Style for last number column
	    WritableCellFormat lastNumcolstyle = new WritableCellFormat();
	    lastNumcolstyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    lastNumcolstyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	    lastNumcolstyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	    lastNumcolstyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    lastNumcolstyle.setAlignment(Alignment.RIGHT);
	    lastNumcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    lastNumcolstyle.setWrap(true);
	    //Style for total row
	    WritableCellFormat totalrowstyle = new WritableCellFormat();
	    totalrowstyle.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
	    totalrowstyle.setAlignment(Alignment.RIGHT);
	    totalrowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalrowstyle.setWrap(true);

	    DateFormat customDateFormat = new DateFormat ("dd.MM.yy");
	    WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
	    dateFormat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
	    dateFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
	    dateFormat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
	    dateFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
	    dateFormat.setAlignment(Alignment.CENTRE);
	    dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
	    
	    int col = cellStart.getC()+1; 
	    int row = cellStart.getR();
	    
	    TempKazContent[] lines = GetLines();
	    
		// Types of cells for
		Label label;
		Number number;
		DateTime time;
		
		int counter = row;
		
		for(int i = row; (i-row) < lines.length; i++) {
	    	number = new Number(col-1, counter, counter-row+1, nn);  	  
	    	sheet.addCell(number);   
	    	label = new Label(col, counter, lines[i-row].ContractNum, valuestyle);        
	    	sheet.addCell(label);
	    	label = new Label(col+1, counter, lines[i-row].BIN, valuestyle);      
	    	sheet.addCell(label);
	    	label = new Label(col+2, counter, lines[i-row].RNN, valuestyle);     
	    	sheet.addCell(label);    	   
	    	label = new Label(col+3, counter, "", valuestyle);     
	    	sheet.addCell(label);    	   
	    	label = new Label(col+4, counter, "", valuestyle);     
	    	sheet.addCell(label); 
	    	label = new Label(col+5, counter, "", valuestyle);     
	    	sheet.addCell(label); 
	    	label = new Label(col+6, counter, "", valuestyle);     
	    	sheet.addCell(label); 
	    	label = new Label(col+7, counter, "", valuestyle);     
	    	sheet.addCell(label); 
	    	number = new Number(col+8, counter, lines[i-row].Summary, numcolstyle);     
	    	sheet.addCell(number);   	   
	    	label = new Label(col+9, counter, lines[i-row].SertificateNum, valuestyle);     
	    	sheet.addCell(label);   	  
	    	label = new Label(col+10, counter, lines[i-row].Series, valuestyle);     
	    	sheet.addCell(label);   
	    	label = new Label(col+11, counter, lines[i-row].IssuingCode, valuestyle);    
	    	sheet.addCell(label);   
	    	
	    	if(lines[i-row].YearIssue != null) {
	    		time = new DateTime(col+12, counter, new java.sql.Date(lines[i-row].YearIssue.getTime()), dateFormat);     
	    		sheet.addCell(time);
	    	}
	    	else {
	    		label = new Label(col+12, counter, "", valuestyle);
	    	    sheet.addCell(label);
	    	}
	    	
	    	if(lines[i-row].DateIssue != null) {
	    		time = new DateTime(col+13, counter, new java.sql.Date(lines[i-row].DateIssue.getTime()), dateFormat);     
	    		sheet.addCell(time);
	    	}
	    	else {
	    		label = new Label(col+13, counter, "", valuestyle);
	    	    sheet.addCell(label);
	    	}

	    	X_cms_GWStype gws = new X_cms_GWStype(getCtx(), lines[i-row].GWS, get_TrxName());
	    	
	    	if (gws.getName() != null) 
	    	{
		    	if(gws.getName().toLowerCase().equals("товар") || gws.getName().toLowerCase().equals("товары")) 
		    	{
		    		number = new Number(col+14, counter, lines[i-row].KZContent, numcolstyle);     
		    		sheet.addCell(number);   
		    	}		    	
		    	else 
		    	{
		    		number = new Number(col+14,counter,0, numcolstyle);
		    		sheet.addCell(number);
		    	}
		    	
		    	if(gws.getName().toLowerCase().equals("работа") || gws.getName().toLowerCase().equals("работы")) 
		    	{
		    		number = new Number(col+15, counter, lines[i-row].KZContent, numcolstyle);     
		    		sheet.addCell(number);   
		    	}
		    	else 
		    	{
		    		number = new Number(col+15,counter,0, numcolstyle);
		    		sheet.addCell(number);
		    	}
		    	
		    	if(gws.getName().toLowerCase().equals("услуга") || gws.getName().toLowerCase().equals("услуги")) 
		    	{
		    		number = new Number(col+16, counter, lines[i-row].KZContent, numcolstyle);     
		    		sheet.addCell(number); 
		    	}
		    	else 
		    	{
		    		number = new Number(col+16,counter,0, numcolstyle);
		    		sheet.addCell(number);
		    	}
	    	}
	    	else {
	    		number = new Number(col+14,counter,0, numcolstyle);
	    		sheet.addCell(number);
	    		number = new Number(col+15,counter,0, numcolstyle);
	    		sheet.addCell(number);
	    		number = new Number(col+16,counter,0, numcolstyle);
	    		sheet.addCell(number);
	    	}
	    	counter++;
	    }
	    
	    copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();

	    try{    
	   		   String fileName = fullPath.toString();
	   		   String command = "excel \""+fileName+"\"";
	   		   Runtime.getRuntime().exec("cmd /c start "+command);
	   	   }
	    catch(Exception e){e.printStackTrace();}
			
	   return Msg.translate(m_ctx, "Success");
	}
	
	private TempKazContent[] GetLines() {
		TempKazContent temp = null;
		List<TempKazContent> list = new ArrayList<TempKazContent>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		StringBuffer sqlQuery = new StringBuffer();
		sqlQuery.append("SELECT c.CMS_Contract_ContractNum, b.FI_BIN_IIN, c.ContractSummary, s.SertificateNum,\n");
		sqlQuery.append(" s.Series, s.IssuingCode, s.YearIssue, s.DateIssue, SUM(s.KzContent), b.FI_TIN, c.CMS_GWStype_ID, c.CMS_Contract_ID\n");
		sqlQuery.append(" FROM Adempiere.CMS_Contract c\n");
		sqlQuery.append(" LEFT JOIN Adempiere.CMS_KazSertificate s ON c.CMS_Contract_ID = s.CMS_Contract_ID\n");
		sqlQuery.append(" LEFT JOIN Adempiere.C_BPartner b ON c.C_BPartner_ID = b.C_BPartner_ID\n");
		sqlQuery.append(" WHERE c.AD_Client_ID = ? AND c.AD_Org_ID = ?\n");
		
		if(Number != "")
			sqlQuery.append(" AND c.CMS_Contract_ID = " + Number);
		if(Series != "")
			sqlQuery.append(" AND s.Series = " + Series);
		if(IssuingCode != "")
			sqlQuery.append(" AND s.issuingcode = " + IssuingCode);
		if(DateIssue != null )
			sqlQuery.append(" AND s.dateissue = " + DateIssue);

		sqlQuery.append(" GROUP BY c.CMS_Contract_ContractNum, b.FI_BIN_IIN, c.ContractSummary, s.Series, s.issuingcode, s.yearissue, s.dateissue, b.FI_TIN, c.cms_GWStype_ID, s.SertificateNum,c.cms_contract_id\n");
		sqlQuery.append(" ORDER BY c.CMS_Contract_ID");
		
		try{
			pstmt = DB.prepareStatement(sqlQuery.toString(),get_TrxName());
			pstmt.setInt(1, AD_Client_ID);
			pstmt.setInt(2, AD_Org_ID);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				temp = new TempKazContent();
				temp.ContractNum = rs.getString(1);
				temp.BIN = rs.getString(2);
				if( rs.getBigDecimal(3)!= null)
					temp.Summary = rs.getBigDecimal(3).doubleValue();
				else temp.Summary = 0.0;
				temp.SertificateNum = rs.getString(4);
				temp.Series = rs.getString(5);
				temp.IssuingCode = rs.getString(6);
				temp.YearIssue = rs.getTimestamp(7);
				temp.DateIssue = rs.getTimestamp(8);
				temp.KZContent = rs.getDouble(
						9);
				temp.RNN = rs.getString(10);
				temp.GWS = rs.getInt(11);
				temp.cms_contract_id = rs.getInt(12);
				
				list.add(temp);
			}
		}
		catch(Exception e)
		{
			s_log.log(Level.SEVERE, "CMS_KazContentReport: ", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return list.toArray(new TempKazContent[list.size()]);
	}
	
	private ArrayList<Integer> cms_contracts = new ArrayList<Integer>();
	
	@SuppressWarnings("deprecation")
	private int getCMS_Contract_ID()
	{
		StringBuffer sql = new StringBuffer();
		
		sql.append("Select cms_roleaccess(");
		sql.append(AD_User_ID + ", ");
		sql.append(AD_Role_ID + ")");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				cms_contracts.add(rs.getInt(1));
			}
		}
		catch(Exception e)
		{
			s_log.log(Level.SEVERE, e.toString());
		}
		finally
		{
			try {rs.close(); pstmt.close();}
			catch(Exception e){}
		}
		
		return cms_contracts.size();
	}
	
	public class TempKazContent
	{
		public TempKazContent()
		{
			
		}
		
		public String ContractNum;
		public String BIN;
		public double Summary;
		public String SertificateNum;
		public String Series;
		public String IssuingCode;
		public Timestamp YearIssue;
		public Timestamp DateIssue;
		public double KZContent;
		public String RNN;
		public int GWS;		
		public int cms_contract_id;
	}
}
